using OrderSystem.Common;
using OrderSystem.SystemFramework;
using System;
using System.Data;
using System.Data.SqlClient;

namespace OrderSystem.DataAccess
{
	public class Orders : IDisposable
	{
		private SqlDataAdapter dataAdapter;

		private SqlCommand selectCommand;

		private SqlCommand insertCommand;

		private SqlCommand updateCommand;

		private SqlCommand deleteCommand;

		public DataTable GetAttachments(string sOrderCode)
		{
			DataTable dataTable = new DataTable("Attachment");
			string cmdText = "select f.PKID as 文件ID,f.FileName as 文件名,f.Description as 描述 from [Files] as f inner join [Order] as o on f.OrderID=o.PKID where o.Code=@OrderCode";
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@OrderCode"].Value = sOrderCode;
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public bool AttachFile(long nOrderID, string sFilename, byte[] fileByte, string sDescription)
		{
			string cmdText = "insert into [Files] values(@OrderID, @FileName, @Data, @Description)";
			try
			{
				this.insertCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.insertCommand.Connection.Open();
				this.insertCommand.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.BigInt));
				this.insertCommand.Parameters.Add(new SqlParameter("@FileName", SqlDbType.NVarChar, 256));
				this.insertCommand.Parameters.Add(new SqlParameter("@Data", SqlDbType.Image));
				this.insertCommand.Parameters.Add(new SqlParameter("@Description", SqlDbType.NVarChar, 256));
				this.insertCommand.Parameters["@OrderID"].Value = nOrderID;
				this.insertCommand.Parameters["@FileName"].Value = sFilename;
				this.insertCommand.Parameters["@Data"].Value = fileByte;
				this.insertCommand.Parameters["@Description"].Value = sDescription;
				this.insertCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool DeleteFile(long nAttachmentID)
		{
			string cmdText = "delete from [Files] where PKID=@AttachmentID";
			try
			{
				this.deleteCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.deleteCommand.Connection.Open();
				this.deleteCommand.Parameters.Add(new SqlParameter("@AttachmentID", SqlDbType.BigInt));
				this.deleteCommand.Parameters["@AttachmentID"].Value = nAttachmentID;
				this.deleteCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public byte[] LoadAttachment(long nPKID)
		{
			byte[] result = null;
			string cmdText = "select Data from [Files] where PKID=@PKID";
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@PKID", SqlDbType.BigInt));
				this.selectCommand.Parameters["@PKID"].Value = nPKID;
				SqlDataReader sqlDataReader = this.selectCommand.ExecuteReader();
				while (sqlDataReader.Read())
				{
					result = (byte[])sqlDataReader["Data"];
				}
				sqlDataReader.Close();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return result;
		}

		public bool UpdateDate(string sOrderCode, string sSpeakForDate, string sDeliveryDate)
		{
			string cmdText = "UPDATE [Order] SET SpeakForDate=@SpeakForDate,DeliveryDate=@DeliveryDate WHERE Code=@OrderCode";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters.Add(new SqlParameter("@SpeakForDate", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters.Add(new SqlParameter("@DeliveryDate", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters["@OrderCode"].Value = sOrderCode;
				this.updateCommand.Parameters["@SpeakForDate"].Value = sSpeakForDate;
				this.updateCommand.Parameters["@DeliveryDate"].Value = sDeliveryDate;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool UpdateRemark(string sOrderCode, string sNewRemark)
		{
			string cmdText = "UPDATE [Order] SET Remark=@Remrk WHERE Code=@OrderCode";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters.Add(new SqlParameter("@Remrk", SqlDbType.NVarChar, 64));
				this.updateCommand.Parameters["@OrderCode"].Value = sOrderCode;
				this.updateCommand.Parameters["@Remrk"].Value = sNewRemark;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool UpdateYaoqiu(string sOrderCode, string sYaoqiu)
		{
			string cmdText = "UPDATE [Order] SET Yaoqiu=@Yaoqiu WHERE Code=@OrderCode";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters.Add(new SqlParameter("@Yaoqiu", SqlDbType.NVarChar, 128));
				this.updateCommand.Parameters["@OrderCode"].Value = sOrderCode;
				this.updateCommand.Parameters["@Yaoqiu"].Value = sYaoqiu;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool UpdateProcessflow(string sOrderCode, string sProcessflow)
		{
			string cmdText = "UPDATE [Order] SET Processflow=@Processflow WHERE Code=@OrderCode";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters.Add(new SqlParameter("@Processflow", SqlDbType.NVarChar, 500));
				this.updateCommand.Parameters["@OrderCode"].Value = sOrderCode;
				this.updateCommand.Parameters["@Processflow"].Value = sProcessflow;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool UpdatesSalesperson(string sOrderCode, string sSalesperson)
		{
			string cmdText = "UPDATE [Order] SET Salesperson=@Salesperson WHERE Code=@OrderCode";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters.Add(new SqlParameter("@Salesperson", SqlDbType.NVarChar, 16));
				this.updateCommand.Parameters["@OrderCode"].Value = sOrderCode;
				this.updateCommand.Parameters["@Salesperson"].Value = sSalesperson;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool UpdateCustomerNameOnPrintedLabel(string sOrderCode, string sCustomerNameOnPrintedLabel)
		{
			string cmdText = "UPDATE [Order] SET CustomerNameOnPrintedLabel=@CustomerNameOnPrintedLabel WHERE Code=@OrderCode";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters.Add(new SqlParameter("@CustomerNameOnPrintedLabel", SqlDbType.NVarChar, 32));
				this.updateCommand.Parameters["@OrderCode"].Value = sOrderCode;
				this.updateCommand.Parameters["@CustomerNameOnPrintedLabel"].Value = sCustomerNameOnPrintedLabel;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public DataSet GetTypeSubtotalByCondition(OrderCondition oc)
		{
			string str = "SELECT pt.[Name] AS 类型,sum(p.Quantity) AS 数量,sum(dbo.ItemSquare(p.Width,p.Height,p.Quantity)) AS 面积,sum(dbo.ItemPerimeter(p.Width,p.Height,p.Quantity)) AS 周长,p.Price as 价格 FROM Product as p inner join [Order] as o on p.OrderID=o.PKID inner join Project as pj on o.ProjectID=pj.PKID inner join Customer as c on pj.CustomerID=c.PKID inner join ProductType AS pt ON p.ProductTypeID=pt.PKID";
			string text = " SELECT ts.technicsName AS 工艺,sum(convert(decimal(12, 4),ts.quantity)*p.Quantity) AS 数量,ts.unitPrice as 单价 FROM Technics as ts inner join Product as p on ts.ProductID=p.PKID inner join [Order] as o on p.OrderID=o.PKID inner join Project as pj on o.ProjectID=pj.PKID inner join Customer as c on pj.CustomerID=c.PKID";
			this.selectCommand = new SqlCommand();
			string sqlCondition = this.GetSqlCondition(oc);
			str = str + sqlCondition + " group by pt.[Name],p.Price order by pt.[Name]";
			text = text + sqlCondition + " group by ts.technicsName,ts.unitPrice order by ts.technicsName";
			DataSet dataSet = new DataSet("TypeSubtotal");
			try
			{
				this.selectCommand.Connection = new SqlConnection(AppSettings.ConnectionString);
				this.selectCommand.CommandText = str + text;
				this.selectCommand.CommandTimeout = 0;
				this.selectCommand.Connection.Open();
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataSet);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataSet;
		}

		public DataSet GetTypeSubtotalByCondition_2(OrderCondition oc)
		{
			string str = "SELECT pt.[Name] AS 产品类型,sum(p.Quantity) AS 订单数量,sum(dbo.ItemSquare(p.Width,p.Height,p.Quantity)) AS 订单面积,sum(dbo.ItemPerimeter(p.Width,p.Height,p.Quantity)) AS 周长 FROM Product as p inner join [Order] as o on p.OrderID=o.PKID inner join Project as pj on o.ProjectID=pj.PKID inner join Customer as c on pj.CustomerID=c.PKID inner join ProductType AS pt ON p.ProductTypeID=pt.PKID";
			string text = " SELECT ts.technicsName AS 工艺,sum(convert(decimal(12, 4),ts.quantity)*p.Quantity) AS 数量 FROM Technics as ts inner join Product as p on ts.ProductID=p.PKID inner join [Order] as o on p.OrderID=o.PKID inner join Project as pj on o.ProjectID=pj.PKID inner join Customer as c on pj.CustomerID=c.PKID";
			string text2 = " SELECT pt.[Name] AS 发货产品类型,sum(ii.Quantity) AS 发货数量,sum(dbo.ItemSquare(p.Width,p.Height,ii.Quantity)) AS 发货面积 FROM Product as p inner join [Order] as o on p.OrderID=o.PKID inner join Project as pj on o.ProjectID=pj.PKID inner join Customer as c on pj.CustomerID=c.PKID inner join ProductType AS pt ON p.ProductTypeID=pt.PKID inner join InvoiceItem as ii on ii.ProductID=p.PKID";
			this.selectCommand = new SqlCommand();
			string sqlCondition = this.GetSqlCondition(oc);
			str = str + sqlCondition + " group by pt.[Name] order by pt.[Name]";
			text = text + sqlCondition + " group by ts.technicsName order by ts.technicsName";
			text2 = text2 + sqlCondition + " group by pt.[Name] order by pt.[Name]";
			DataSet dataSet = new DataSet("TypeSubtotal");
			try
			{
				this.selectCommand.Connection = new SqlConnection(AppSettings.ConnectionString);
				this.selectCommand.CommandText = str + text + text2;
				this.selectCommand.CommandTimeout = 0;
				this.selectCommand.Connection.Open();
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataSet);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataSet;
		}

		public DataSet GetTypeSubtotalByCondition_3(OrderCondition oc)
		{
			string str = "select c.CompanyName as 公司名称,pt.[Name] AS 类型,sum(p.Quantity) as 数量,sum(dbo.ItemSquare(p.Width,p.Height,p.Quantity)) AS 面积 FROM Product as p inner join [Order] as o on p.OrderID=o.PKID inner join Project as pj on o.ProjectID=pj.PKID inner join Customer as c on pj.CustomerID=c.PKID inner join ProductType AS pt ON p.ProductTypeID=pt.PKID";
			string text = " SELECT c.CompanyName as 公司名称,ts.technicsName AS 工艺,sum(convert(decimal(12, 4),ts.quantity)*p.Quantity) AS 数量 FROM Technics as ts inner join Product as p on ts.ProductID=p.PKID inner join [Order] as o on p.OrderID=o.PKID inner join Project as pj on o.ProjectID=pj.PKID inner join Customer as c on pj.CustomerID=c.PKID";
			string text2 = " select c.CompanyName as 公司名称,pt.[Name] AS 类型,sum(p.Quantity) as 数量,sum(dbo.ItemSquare(p.Width,p.Height,p.Quantity)) AS 面积 FROM Product as p inner join [Order] as o on p.OrderID=o.PKID inner join Project as pj on o.ProjectID=pj.PKID inner join Customer as c on pj.CustomerID=c.PKID inner join ProductType AS pt ON p.ProductTypeID=pt.PKID";
			this.selectCommand = new SqlCommand();
			string sqlCondition = this.GetSqlCondition(oc);
			str = str + sqlCondition + " group by c.CompanyName, pt.[Name] order by c.CompanyName, pt.[Name]";
			text = text + sqlCondition + " group by c.CompanyName,ts.technicsName order by c.CompanyName,ts.technicsName";
			text2 = text2 + sqlCondition + " and o.Payed=0 group by c.CompanyName, pt.[Name] order by c.CompanyName, pt.[Name]";
			DataSet dataSet = new DataSet("TypeSubtotal");
			try
			{
				this.selectCommand.Connection = new SqlConnection(AppSettings.ConnectionString);
				this.selectCommand.CommandText = str + text + text2;
				this.selectCommand.CommandTimeout = 0;
				this.selectCommand.Connection.Open();
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataSet);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataSet;
		}

		public DataTable GetTypeSubtotalByCondition_4(OrderCondition oc)
		{
			string text = "select c.CompanyName AS 客户,sum(dbo.TotalValueOfOrder(o.Code)) AS 金额 FROM [Order] AS o INNER JOIN Project AS pj ON o.ProjectID=pj.PKID INNER JOIN Customer AS c ON pj.CustomerID=c.PKID";
			this.selectCommand = new SqlCommand();
			string sqlCondition = this.GetSqlCondition(oc);
			text = text + sqlCondition + " group by c.CompanyName order by 金额";
			DataTable dataTable = new DataTable("CoustomerMoney");
			try
			{
				this.selectCommand.Connection = new SqlConnection(AppSettings.ConnectionString);
				this.selectCommand.CommandText = text;
				this.selectCommand.CommandTimeout = 0;
				this.selectCommand.Connection.Open();
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public DataSet GetTypeSubtotalOfOrder(string sOrderCode)
		{
			string str = "SELECT pt.[Name] AS 类型,sum(p.Quantity) AS 数量,sum(dbo.ItemSquare(p.Width,p.Height,p.Quantity)) AS 面积,sum(dbo.ItemPerimeter(p.Width,p.Height,p.Quantity)) AS 周长,p.Price as 价格 FROM Product AS p inner join [Order] as o on p.OrderID=o.PKID inner join ProductType AS pt ON p.ProductTypeID=pt.PKID WHERE o.Code=@OrderCode group by pt.[Name],p.Price";
			string str2 = " SELECT ts.technicsName AS 工艺,sum(convert(decimal(12, 4),ts.quantity)*p.Quantity) AS 数量,ts.unitPrice as 单价 FROM Technics as ts inner join Product AS p on ts.ProductID=p.PKID inner join [Order] as o on p.OrderID=o.PKID WHERE o.Code=@OrderCode group by ts.technicsName,ts.unitPrice order by ts.technicsName";
			DataSet dataSet = new DataSet("TypeSubtotal");
			try
			{
				this.selectCommand = new SqlCommand(str + str2, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@OrderCode"].Value = sOrderCode;
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataSet);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataSet;
		}

		public int GetNumberOfRows(string sOrderCode)
		{
			string cmdText = "SELECT COUNT(p.PKID) FROM [Product] AS p INNER JOIN [Order] AS o ON p.OrderID=o.PKID WHERE o.Code=@OrderCode";
			int result = 0;
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@OrderCode"].Value = sOrderCode;
				string text = this.selectCommand.ExecuteScalar().ToString();
				if (text != "")
				{
					result = Convert.ToInt32(text);
				}
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return result;
		}

		public double GetTotalSquareForOrder(string sOrderCode)
		{
			string cmdText = "SELECT dbo.TotalSquareOfOrder(@OrderCode)";
			double result = 0.0;
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@OrderCode"].Value = sOrderCode;
				string text = this.selectCommand.ExecuteScalar().ToString();
				if (text != "")
				{
					result = Convert.ToDouble(text);
				}
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return result;
		}

		public int GetAmountToOrder(string sOrderCode)
		{
			string cmdText = "SELECT dbo.AmountToOrder(@OrderCode)";
			int result = 0;
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@OrderCode"].Value = sOrderCode;
				string text = this.selectCommand.ExecuteScalar().ToString();
				if (text != "")
				{
					result = Convert.ToInt32(text);
				}
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return result;
		}

		public string GetDate(string sProductID)
		{
			string cmdText = "select SpeakForDate from [Order] as o inner join Product as p on o.PKID=p.OrderID where p.PKID=@ProductID";
			string result = null;
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@ProductID"].Value = sProductID;
				result = this.selectCommand.ExecuteScalar().ToString();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return result;
		}

		public string GetOrderCode(string sInvoiceCode)
		{
			string cmdText = "SELECT dbo.GetOrderCodeInInvoice(@InvoiceCode)";
			string result = null;
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@InvoiceCode", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@InvoiceCode"].Value = sInvoiceCode;
				result = this.selectCommand.ExecuteScalar().ToString();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return result;
		}

		public DataTable GetSalespersons()
		{
			string cmdText = "select distinct Salesperson from [Order]";
			DataTable dataTable = new DataTable("Salesperson");
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public int GetPrintedQuantity(string sOrderCode)
		{
			string cmdText = "SELECT PrintedQuantity from [Order] where Code=@OrderCode";
			int result = 0;
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@OrderCode"].Value = sOrderCode;
				object obj = this.selectCommand.ExecuteScalar();
				if (obj != null)
				{
					result = Convert.ToInt32(obj);
				}
				else
				{
					result = -1;
				}
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return result;
		}

		public bool PrintOnce(string sOrderCode)
		{
			string cmdText = "Update [Order] set PrintedQuantity=PrintedQuantity+1 where Code=@OrderCode";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters["@OrderCode"].Value = sOrderCode;
				this.updateCommand.ExecuteScalar();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public int GetPrintedQuantityForLabel(string sOrderCode)
		{
			string cmdText = "SELECT LabelPrintedQuantity from [Order] where Code=@OrderCode";
			int result = 0;
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@OrderCode"].Value = sOrderCode;
				object obj = this.selectCommand.ExecuteScalar();
				if (obj != null)
				{
					result = Convert.ToInt32(obj);
				}
				else
				{
					result = -1;
				}
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return result;
		}

		public bool PrintOnceForLabel(string sOrderCode)
		{
			string cmdText = "Update [Order] set LabelPrintedQuantity=LabelPrintedQuantity+1 where Code=@OrderCode";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters["@OrderCode"].Value = sOrderCode;
				this.updateCommand.ExecuteScalar();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool Edited(long nOrderID, bool isEdited)
		{
			string cmdText = " SET XACT_ABORT ON BEGIN TRANSACTION UPDATE [Order] SET Edited=@Edited WHERE PKID=@OrderID delete EditingOrder where OrderID=@OrderID COMMIT TRANSACTION";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.BigInt));
				this.updateCommand.Parameters["@OrderID"].Value = nOrderID;
				this.updateCommand.Parameters.Add(new SqlParameter("@Edited", SqlDbType.Bit));
				this.updateCommand.Parameters["@Edited"].Value = isEdited;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool Lailiao(string sOrderCode)
		{
			string cmdText = "declare @IsQuliao bit select @IsQuliao=Quliao from [Order] where code=@OrderCode if @IsQuliao=1 begin   raiserror ('此订单已经设置了去料，不能在设置来料！', 16, 1)   return end UPDATE [Order] SET Lailiao=1 WHERE Code=@OrderCode";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters["@OrderCode"].Value = sOrderCode;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool UnLailiao(string sOrderCode)
		{
			string cmdText = "UPDATE [Order] SET Lailiao=0 WHERE Code=@OrderCode";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters["@OrderCode"].Value = sOrderCode;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool Quliao(string sOrderCode)
		{
			string cmdText = "declare @IsLailiao bit select @IsLailiao=Lailiao from [Order] where code=@OrderCode if @IsLailiao=1 begin   raiserror ('此订单已经设置了来料，不能在设置去料！', 16, 1)   return end UPDATE [Order] SET Quliao=1 WHERE Code=@OrderCode";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters["@OrderCode"].Value = sOrderCode;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool UnQuliao(string sOrderCode)
		{
			string cmdText = "UPDATE [Order] SET Quliao=0 WHERE Code=@OrderCode";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters["@OrderCode"].Value = sOrderCode;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool FinishOrder(string sOrderCode)
		{
			try
			{
				this.updateCommand = new SqlCommand("FinishOrder", new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.CommandType = CommandType.StoredProcedure;
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters["@OrderCode"].Value = sOrderCode;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool LockData(long nOrderID)
		{
			try
			{
				this.updateCommand = new SqlCommand("LockOrder", new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.CommandType = CommandType.StoredProcedure;
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.BigInt));
				this.updateCommand.Parameters["@OrderID"].Value = nOrderID;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool UnLockData(string orderCode)
		{
			string cmdText = "UPDATE [Order] SET ProductName='', ProductName_2='', Quantity=0, Area=0, Amount=0, Locked=0 WHERE Code=@OrderCode";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters["@OrderCode"].Value = orderCode;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool Activate(long nOrderID)
		{
			string cmdText = "UPDATE [Order] SET Cancelled=0 WHERE PKID=@OrderID";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.BigInt));
				this.updateCommand.Parameters["@OrderID"].Value = nOrderID;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool Cancel(long nOrderID)
		{
			string cmdText = "UPDATE [Order] SET Cancelled=1 WHERE PKID=@OrderID";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.BigInt));
				this.updateCommand.Parameters["@OrderID"].Value = nOrderID;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public DataTable LoadOrderByOrderCode(string sOrderCode)
		{
			DataTable dataTable = new DataTable("Order");
			string cmdText = "SELECT o.PKID AS 订单序号,o.Code AS 订单号,c.PKID as 客户序号,c.CompanyName AS 公司名称,c.Linkman AS 公司联系人,pj.PKID AS 计划序号,pj.ProjectName AS 计划名称,pj.Linkman AS 计划联系人,pj.LinkmanPhone AS 计划联系人电话,pj.ProjectAddress AS 计划地址,o.OrderDate AS 下单日期,o.SpeakForDate AS 订单日期,o.DeliveryDate AS 交货日期,o.Lailiao as 是否来料,o.Quliao as 是否去料,o.Payed as 是否结算,(select Payment.Code from Payment where PKID=o.PaymentID) as 结算序号,o.ActualPayment AS 实际付款,o.Edited AS 编辑完成,o.Locked AS 是否锁定,o.Cancelled AS 是否作废,o.LockedFlowcard as 锁定流程卡,o.Remark AS 订单备注,o.Yaoqiu AS 加工要求,o.Processflow as 工艺流程,o.CustomerNameOnPrintedLabel AS 标签上的客户名称,op.PKID AS 操作者ID,op.[Name] AS 操作者,o.Salesperson AS 业务员 FROM [Order] AS o INNER JOIN Project AS pj ON o.ProjectID=pj.PKID INNER JOIN Customer AS c ON pj.CustomerID=c.PKID INNER JOIN Operator AS op ON o.OperatorID=op.PKID WHERE o.Code=@OrderCode";
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@OrderCode"].Value = sOrderCode;
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public DataTable LoadOrderForUpdate(string sOrderCode)
		{
			DataTable dataTable = new DataTable("Order");
			string cmdText = "SELECT o.PKID,o.Code,c.PKID,c.CompanyName,pj.PKID,pj.ProjectName,pj.Linkman,o.SpeakForDate,o.Remark,op.[Name] FROM [Order] AS o INNER JOIN Project AS pj ON o.ProjectID=pj.PKID INNER JOIN Customer AS c ON pj.CustomerID=c.PKID INNER JOIN Operator AS op ON o.OperatorID=op.PKID WHERE o.Code=@OrderCode";
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@OrderCode"].Value = sOrderCode;
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public DataTable GetOrdersByCondition(OrderCondition oc)
		{
			this.selectCommand = new SqlCommand();
			string sqlCondition = this.GetSqlCondition(oc);
			DataTable dataTable = new DataTable("Order");
			string text = "SELECT o.PKID AS 订单ID,o.Code AS 订单号,c.CompanyName AS 客户,pj.ProjectName AS 计划,convert(char(10),o.SpeakForDate,120) AS 订购日期,o.Edited as 编辑完成,o.Locked AS 锁定数据,o.Cancelled AS 订单作废,o.Remark AS 备注,op.Name AS 操作者,o.PrintedQuantity as 订单生成次数,o.LabelPrintedQuantity 标签打印次数,o.CustomerNameOnPrintedLabel AS 标签上的客户名称,dbo.GetFilesOfOrder(o.Code) as 附件 FROM [Order] AS o INNER JOIN Project AS pj ON o.ProjectID=pj.PKID INNER JOIN Customer AS c ON pj.CustomerID=c.PKID INNER JOIN Operator AS op ON o.OperatorID=op.PKID";
			text = text + sqlCondition + " ORDER BY c.[CompanyName],o.PKID";
			try
			{
				this.selectCommand.Connection = new SqlConnection(AppSettings.ConnectionString);
				this.selectCommand.CommandText = text;
				this.selectCommand.CommandTimeout = 0;
				this.selectCommand.Connection.Open();
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public DataTable GetSumUpByCondition(OrderCondition oc)
		{
			this.selectCommand = new SqlCommand();
			string sqlCondition = this.GetSqlCondition(oc);
			DataTable dataTable = new DataTable("SumUp");
			string text = "SELECT o.PKID AS 订单ID,o.Code AS 订单号,c.CompanyName AS 公司名称,pj.ProjectName AS 计划名称,convert(char(10),o.SpeakForDate,120) AS 订单日期,o.DeliveryDate AS 交货日期,o.Quantity AS 数量,o.Area AS 面积,o.Amount AS 金额,o.Payed as '是否结算',( select Code from Payment where PKID = o.PaymentID ) as '结算号',o.Locked AS '订单锁定',o.Cancelled AS '订单作废'," + (LoginInfo.Role[PowerName.OrderData_SeePrice] ? "ProductName_2 as 类型" : "ProductName as 类型") + ",o.Remark AS 订单备注,op.[Name] AS 操作者,o.Salesperson as 业务员 FROM [Order] AS o INNER JOIN Project AS pj ON o.ProjectID=pj.PKID INNER JOIN Customer AS c ON pj.CustomerID=c.PKID INNER JOIN Operator AS op ON o.OperatorID=op.PKID";
			text = text + sqlCondition + " ORDER BY o.PKID";
			try
			{
				this.selectCommand.Connection = new SqlConnection(AppSettings.ConnectionString);
				this.selectCommand.CommandText = text;
				this.selectCommand.CommandTimeout = 0;
				this.selectCommand.Connection.Open();
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public DataTable GetInvoiceDetailOfOrder(OrderCondition oc)
		{
			this.selectCommand = new SqlCommand();
			string sqlCondition = this.GetSqlCondition(oc);
			DataTable dataTable = new DataTable("Detail");
			string commandText = "SELECT o.PKID AS 订单序号,o.Code AS 订单号,c.CompanyName AS 客户,pj.ProjectName AS 计划, o.ProductName as 类型,o.SpeakForDate AS 订单日期,o.DeliveryDate AS 交货日期,o.Quantity AS 总数量, case when (select sum(ii.Quantity) from InvoiceItem as ii inner join Product as p on ii.ProductID=p.PKID where p.OrderID=o.PKID) is null then 0 ELSE (select sum(ii.Quantity) from InvoiceItem as ii inner join Product as p on ii.ProductID=p.PKID where p.OrderID=o.PKID) end AS 已发货,o.Area AS 总面积, case when cast( (select sum(dbo.ItemSquare(p.Width, p.Height, ii.Quantity)) from InvoiceItem as ii inner join Product as p on ii.ProductID=p.PKID where p.OrderID=o.PKID) as decimal(18,2)) is null then 0 ELSE cast( (select sum(dbo.ItemSquare(p.Width, p.Height, ii.Quantity)) from InvoiceItem as ii inner join Product as p on ii.ProductID=p.PKID where p.OrderID=o.PKID) as decimal(18,2)) end AS 已发货面积,o.Remark AS 订单备注 FROM [Order] AS o INNER JOIN Project AS pj ON o.ProjectID=pj.PKID INNER JOIN Customer AS c ON pj.CustomerID=c.PKID" + sqlCondition + " ORDER BY c.[CompanyName],o.PKID";
			try
			{
				this.selectCommand.Connection = new SqlConnection(AppSettings.ConnectionString);
				this.selectCommand.CommandText = commandText;
				this.selectCommand.CommandTimeout = 0;
				this.selectCommand.Connection.Open();
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public DataTable GetOrderDetailForTempered(OrderCondition oc)
		{
			this.selectCommand = new SqlCommand();
			string sqlCondition = this.GetSqlCondition(oc);
			DataTable dataTable = new DataTable("Detail");
			string commandText = "SELECT o.PKID AS 订单序号,o.Code AS 订单号,c.CompanyName AS 客户,pj.ProjectName AS 计划,o.SpeakForDate AS 订单日期,o.DeliveryDate AS 交货日期,o.Quantity AS 总数量,dbo.AmountToTemperedInOrder(o.Code) AS 已钢化,o.Area AS 总面积,dbo.TotalTemperedSquareOfOrder(o.Code) AS 已钢化面积 FROM [Order] AS o INNER JOIN Project AS pj ON o.ProjectID=pj.PKID INNER JOIN Customer AS c ON pj.CustomerID=c.PKID" + sqlCondition + " ORDER BY c.[CompanyName],o.PKID";
			try
			{
				this.selectCommand.Connection = new SqlConnection(AppSettings.ConnectionString);
				this.selectCommand.CommandText = commandText;
				this.selectCommand.CommandTimeout = 0;
				this.selectCommand.Connection.Open();
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public DataTable GetNosettlementOrdersByCondition(OrderCondition oc)
		{
			this.selectCommand = new SqlCommand();
			string sqlCondition = this.GetSqlCondition(oc);
			DataTable dataTable = new DataTable("SumUp");
			string text = "SELECT convert(bit,0) as 是否结算,o.PKID AS 订单序号,o.Code AS 订单号,c.CompanyName AS 公司名称,pj.ProjectName AS 计划名称,convert(char(10),o.SpeakForDate,120) AS 订单日期,o.DeliveryDate AS 交货日期,o.Quantity as 数量, o.Area as 面积, o.Amount as 金额,o.Payed as '结算状态',o.Locked AS '订单锁定',o.Cancelled AS '订单作废',dbo.GetProductTypeOfOrder_1(o.Code) as 类型,o.Remark AS 订单备注,op.[Name] AS 操作者,o.Salesperson as 业务员 FROM [Order] AS o INNER JOIN Project AS pj ON o.ProjectID=pj.PKID INNER JOIN Customer AS c ON pj.CustomerID=c.PKID INNER JOIN Operator AS op ON o.OperatorID=op.PKID";
			text = text + sqlCondition + " and o.Payed=0 and o.Locked=1 and o.Cancelled=0 ORDER BY o.PKID";
			try
			{
				this.selectCommand.Connection = new SqlConnection(AppSettings.ConnectionString);
				this.selectCommand.CommandText = text;
				this.selectCommand.CommandTimeout = 0;
				this.selectCommand.Connection.Open();
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public string InsertOrder(string sOrderCode, int projectID, string sSpeakForDate, string sDeliveryDate, bool isLailiao, bool isQuliao, string sRemark, int nOperatorID, ref string s)
		{
			string result = null;
            result = GetCurrentNumber();
            sOrderCode = result;
            try
			{
				this.insertCommand = new SqlCommand("InsertOrder", new SqlConnection(AppSettings.ConnectionString));
				this.insertCommand.CommandType = CommandType.StoredProcedure;
				this.insertCommand.Connection.Open();
				this.insertCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.insertCommand.Parameters["@OrderCode"].Value = sOrderCode;
				this.insertCommand.Parameters["@OrderCode"].Direction = ParameterDirection.InputOutput;
				this.insertCommand.Parameters.Add(new SqlParameter("@ProjectID", SqlDbType.Int));
				this.insertCommand.Parameters["@ProjectID"].Value = projectID;
				this.insertCommand.Parameters.Add(new SqlParameter("@SpeakForDate", SqlDbType.VarChar));
				this.insertCommand.Parameters["@SpeakForDate"].Value = sSpeakForDate;
				this.insertCommand.Parameters.Add(new SqlParameter("@DeliveryDate", SqlDbType.VarChar));
				this.insertCommand.Parameters["@DeliveryDate"].Value = sDeliveryDate;
				this.insertCommand.Parameters.Add(new SqlParameter("@Lailiao", SqlDbType.Bit));
				this.insertCommand.Parameters["@Lailiao"].Value = isLailiao;
				this.insertCommand.Parameters.Add(new SqlParameter("@Quliao", SqlDbType.Bit));
				this.insertCommand.Parameters["@Quliao"].Value = isQuliao;
				this.insertCommand.Parameters.Add(new SqlParameter("@Remark", SqlDbType.NVarChar, 64));
				this.insertCommand.Parameters["@Remark"].Value = sRemark;
				this.insertCommand.Parameters.Add(new SqlParameter("@OperatorID", SqlDbType.Int));
				this.insertCommand.Parameters["@OperatorID"].Value = nOperatorID;
				this.insertCommand.Parameters.Add(new SqlParameter("@s", SqlDbType.NVarChar, 128));
				this.insertCommand.Parameters["@s"].Value = "";
				this.insertCommand.Parameters["@s"].Direction = ParameterDirection.InputOutput;
				this.insertCommand.ExecuteNonQuery();
                //result = this.insertCommand.Parameters["@OrderCode"].Value.ToString();
                s = this.insertCommand.Parameters["@s"].Value.ToString();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return result;
		}

        public string GetCurrentNumber()
        {
            OrderCondition oc = new OrderCondition();
            string currentNumber = string.Empty;
            this.selectCommand = new SqlCommand();
            string sqlCondition = this.GetSqlCondition(oc);
            DataTable dataTable = new DataTable("CurrentNumberTable");
            string commandText = "SELECT max(CurrentNumber) as CurrentNumber from OrderNumber WHERE StartDate='" + DateTime.Now.ToString("yyyy-MM-dd") + "'";
            try
            {
                this.selectCommand.Connection = new SqlConnection(AppSettings.ConnectionString);
                this.selectCommand.CommandText = commandText;
                this.selectCommand.CommandTimeout = 0;
                this.selectCommand.Connection.Open();
                this.dataAdapter = new SqlDataAdapter(this.selectCommand);
                this.dataAdapter.Fill(dataTable);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            if (dataTable != null && dataTable.Rows.Count > 0 && !string.IsNullOrEmpty(dataTable.Rows[0]["CurrentNumber"].ToString()))
            {
                currentNumber = dataTable.Rows[0]["CurrentNumber"].ToString();
                long count = long.Parse(currentNumber);
                currentNumber = (count + 1).ToString();
            }
            else
            {
                currentNumber = DateTime.Now.ToString("yyyyMMdd") + "1";
            }
            try
            {
                string sqlText = "insert into OrderNumber values(@StartDate, @CurrentNumber)";

                this.insertCommand = new SqlCommand(sqlText, new SqlConnection(AppSettings.ConnectionString));
                this.insertCommand.Connection.Open();
                this.insertCommand.Parameters.Add(new SqlParameter("@StartDate", SqlDbType.DateTime));
                this.insertCommand.Parameters["@StartDate"].Value = DateTime.Now.ToString("yyyy-MM-dd");
                this.insertCommand.Parameters.Add(new SqlParameter("@CurrentNumber", SqlDbType.Int));
                this.insertCommand.Parameters["@CurrentNumber"].Value = currentNumber;
                this.insertCommand.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }

            return currentNumber;
        }


        public bool UpdateOrder(string sOrderCode, int nProjectID, string sRemark)
		{
			string cmdText = "UPDATE [Order] SET ProjectID=@ProjectID,Remark=@Remrk WHERE Code=@OrderCode";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters.Add(new SqlParameter("@ProjectID", SqlDbType.Int));
				this.updateCommand.Parameters.Add(new SqlParameter("@Remrk", SqlDbType.NVarChar, 64));
				this.updateCommand.Parameters["@OrderCode"].Value = sOrderCode;
				this.updateCommand.Parameters["@ProjectID"].Value = nProjectID;
				this.updateCommand.Parameters["@Remrk"].Value = sRemark;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool Delete(OrderCondition oc)
		{
			this.selectCommand = new SqlCommand();
			string sqlCondition = this.GetSqlCondition(oc);
			string commandText = string.Concat(new string[]
			{
				" SET XACT_ABORT ON BEGIN TRANSACTION Delete [Files] from [Files] as f inner join [Order] AS o on f.OrderID=o.PKID INNER JOIN Project AS pj ON o.ProjectID=pj.PKID INNER JOIN Customer AS c ON pj.CustomerID=c.PKID INNER JOIN Operator AS op ON o.OperatorID=op.PKID",
				sqlCondition,
				" DELETE [Order] FROM [Order] AS o INNER JOIN Project AS pj ON o.ProjectID=pj.PKID INNER JOIN Customer AS c ON pj.CustomerID=c.PKID INNER JOIN Operator AS op ON o.OperatorID=op.PKID",
				sqlCondition,
				" COMMIT TRANSACTION"
			});
			try
			{
				this.selectCommand.Connection = new SqlConnection(AppSettings.ConnectionString);
				this.selectCommand.CommandText = commandText;
				this.selectCommand.CommandTimeout = 0;
				this.selectCommand.Connection.Open();
				this.selectCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool Delete(long nOrderID)
		{
			string cmdText = " SET XACT_ABORT ON BEGIN TRANSACTION Delete [Files] from [Files] as f inner join [Order] AS o on f.OrderID=o.PKID INNER JOIN Project AS pj ON o.ProjectID=pj.PKID INNER JOIN Customer AS c ON pj.CustomerID=c.PKID INNER JOIN Operator AS op ON o.OperatorID=op.PKID where o.PKID=@OrderID DELETE [Order] FROM [Order] AS o INNER JOIN Project AS pj ON o.ProjectID=pj.PKID INNER JOIN Customer AS c ON pj.CustomerID=c.PKID INNER JOIN Operator AS op ON o.OperatorID=op.PKID where o.PKID=@OrderID COMMIT TRANSACTION";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.BigInt));
				this.updateCommand.Parameters["@OrderID"].Value = nOrderID;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool StartUseOrder(long nOrderID, int nOperatorID, string strIPAddress)
		{
			string cmdText = " declare @t_OperatorID int set @t_OperatorID=NULL declare @t_OperatorName nvarchar(32) set @t_OperatorName=NULL select @t_OperatorID=OperatorID from [EditingOrder] where OrderID=@OrderID if @t_OperatorID IS Not NULL begin   select @t_OperatorName=[Name] From Operator where PKID=@t_OperatorID" + "   if @t_OperatorID<>@OperatorID".Replace("@OperatorID", nOperatorID.ToString()) + "   begin       declare @ERR nvarchar(64) set @ERR='[db] 此订单正在被 '+@t_OperatorName+' 编辑...'       raiserror(@ERR,16,1)       return   end end SET XACT_ABORT ON BEGIN TRANSACTION if @t_OperatorID IS NULL begin   Insert Into [EditingOrder](OrderID,OperatorID,IPAddress,StartUseTime) Values(@OrderID,@OperatorID,@IPAddress,GetDate()) end COMMIT TRANSACTION";
			try
			{
				this.insertCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.insertCommand.Connection.Open();
				this.insertCommand.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.BigInt));
				this.insertCommand.Parameters["@OrderID"].Value = nOrderID;
				this.insertCommand.Parameters.Add(new SqlParameter("@OperatorID", SqlDbType.Int));
				this.insertCommand.Parameters["@OperatorID"].Value = nOperatorID;
				this.insertCommand.Parameters.Add(new SqlParameter("@IPAddress", SqlDbType.VarChar, 32));
				this.insertCommand.Parameters["@IPAddress"].Value = strIPAddress;
				this.insertCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool StopUseOrder(long nOrderID)
		{
			string cmdText = "DELETE FROM [EditingOrder] WHERE OrderID=@OrderID";
			try
			{
				this.deleteCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.deleteCommand.Connection.Open();
				this.deleteCommand.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.BigInt));
				this.deleteCommand.Parameters["@OrderID"].Value = nOrderID;
				this.deleteCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public int GetNeverPrint(OrderCondition oc)
		{
			this.selectCommand = new SqlCommand();
			string commandText = "SELECT count(*) from [Order] as o" + this.GetSqlCondition(oc);
			int result = 0;
			try
			{
				this.selectCommand.Connection = new SqlConnection(AppSettings.ConnectionString);
				this.selectCommand.CommandText = commandText;
				this.selectCommand.CommandTimeout = 0;
				this.selectCommand.Connection.Open();
				string text = this.selectCommand.ExecuteScalar().ToString();
				if (text != "")
				{
					result = Convert.ToInt32(text);
				}
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return result;
		}

		private string GetSqlCondition(OrderCondition oc)
		{
			string text = "";
			if (oc.CustomerID > -1)
			{
				if (text == "")
				{
					text += "pj.CustomerID=@CustomerID";
				}
				else
				{
					text += " AND pj.CustomerID=@CustomerID";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.Int));
				this.selectCommand.Parameters["@CustomerID"].Value = oc.CustomerID;
			}
			if (oc.ProjectID > -1)
			{
				if (text == "")
				{
					text += "o.ProjectID=@ProjectID";
				}
				else
				{
					text += " AND o.ProjectID=@ProjectID";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@ProjectID", SqlDbType.Int));
				this.selectCommand.Parameters["@ProjectID"].Value = oc.ProjectID;
			}
			if (oc.SpeakForDateStart != null)
			{
				if (text == "")
				{
					text += "o.SpeakForDate>=CONVERT(DATETIME,@SpeakForDateStart) AND o.SpeakForDate<=CONVERT(DATETIME,@SpeakForDateEnd)";
				}
				else
				{
					text += " AND o.SpeakForDate>=CONVERT(DATETIME,@SpeakForDateStart) AND o.SpeakForDate<=CONVERT(DATETIME,@SpeakForDateEnd)";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@SpeakForDateStart", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@SpeakForDateStart"].Value = oc.SpeakForDateStart;
				this.selectCommand.Parameters.Add(new SqlParameter("@SpeakForDateEnd", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@SpeakForDateEnd"].Value = oc.SpeakForDateEnd;
			}
			if (oc.DeliveryDateStart != null)
			{
				if (text == "")
				{
					text += "o.DeliveryDate>=CONVERT(DATETIME,@DeliveryDateStart) AND o.DeliveryDate<=CONVERT(DATETIME,@DeliveryDateEnd)";
				}
				else
				{
					text += " AND o.SpeakForDate>=CONVERT(DATETIME,@DeliveryDateStart) AND o.DeliveryDate<=CONVERT(DATETIME,@DeliveryDateEnd)";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@DeliveryDateStart", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@DeliveryDateStart"].Value = oc.DeliveryDateStart;
				this.selectCommand.Parameters.Add(new SqlParameter("@DeliveryDateEnd", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@DeliveryDateEnd"].Value = oc.DeliveryDateEnd;
			}
			if (oc.IsLailiao > -1)
			{
				if (text == "")
				{
					text += "o.Lailiao=@Lailiao";
				}
				else
				{
					text += " AND o.Lailiao=@Lailiao";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Lailiao", SqlDbType.Bit));
				this.selectCommand.Parameters["@Lailiao"].Value = oc.IsLailiao;
			}
			if (oc.IsQuliao > -1)
			{
				if (text == "")
				{
					text += "o.Quliao=@Quliao";
				}
				else
				{
					text += " AND o.Quliao=@Quliao";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Quliao", SqlDbType.Bit));
				this.selectCommand.Parameters["@Quliao"].Value = oc.IsQuliao;
			}
			if (oc.Locked > -1)
			{
				if (text == "")
				{
					text += "o.Locked=@Locked";
				}
				else
				{
					text += " AND o.Locked=@Locked";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Locked", SqlDbType.Bit));
				this.selectCommand.Parameters["@Locked"].Value = oc.Locked;
			}
			if (oc.Cancelled > -1)
			{
				if (text == "")
				{
					text += "o.Cancelled=@Cancelled";
				}
				else
				{
					text += " AND o.Cancelled=@Cancelled";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Cancelled", SqlDbType.Bit));
				this.selectCommand.Parameters["@Cancelled"].Value = oc.Cancelled;
			}
			if (oc.PayDateStart != null)
			{
				if (text == "")
				{
					text += "o.PayedDate>=CONVERT(DATETIME,@PayedDateStart) AND o.PayedDate<=CONVERT(DATETIME,@PayedDateEnd)";
				}
				else
				{
					text += " AND o.PayedDate>=CONVERT(DATETIME,@PayedDateStart) AND o.PayedDate<=CONVERT(DATETIME,@PayedDateEnd)";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@PayedDateStart", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@PayedDateStart"].Value = oc.PayDateStart;
				this.selectCommand.Parameters.Add(new SqlParameter("@PayedDateEnd", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@PayedDateEnd"].Value = oc.PayDateEnd;
			}
			if (oc.OrderCode != null)
			{
				if (text == "")
				{
					text += "o.Code LIKE '%'+@OrderCode+'%'";
				}
				else
				{
					text += " AND o.Code LIKE '%'+@OrderCode+'%'";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@OrderCode"].Value = oc.OrderCode.Replace(" ", "%");
			}
			if (oc.Remark != null)
			{
				if (text == "")
				{
					text += "o.Remark LIKE '%'+@Remark+'%'";
				}
				else
				{
					text += " AND o.Remark LIKE '%'+@Remark+'%'";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Remark", SqlDbType.NVarChar, 64));
				this.selectCommand.Parameters["@Remark"].Value = oc.Remark.Replace(" ", "%");
			}
			if (oc.Salesperson != null)
			{
				if (text == "")
				{
					text += "o.Salesperson LIKE '%'+@Salesperson+'%'";
				}
				else
				{
					text += " AND o.Salesperson LIKE '%'+@Salesperson+'%'";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Salesperson", SqlDbType.NVarChar, 16));
				this.selectCommand.Parameters["@Salesperson"].Value = oc.Salesperson.Replace(" ", "%");
			}
			if (oc.LabelPrinted > -1)
			{
				if (text == "")
				{
					if (oc.LabelPrinted == 0)
					{
						text += "o.LabelPrintedQuantity = 0";
					}
					else
					{
						text += "o.LabelPrintedQuantity > 0";
					}
				}
				else if (oc.LabelPrinted == 0)
				{
					text += " AND o.LabelPrintedQuantity = 0";
				}
				else
				{
					text += " AND o.LabelPrintedQuantity > 0";
				}
			}
			if (text != "")
			{
				text = " WHERE " + text;
			}
			return text;
		}

		public void Dispose()
		{
			this.Dispose(true);
			GC.SuppressFinalize(true);
		}

		protected virtual void Dispose(bool disposing)
		{
			if (disposing)
			{
				if (this.dataAdapter != null)
				{
					if (this.dataAdapter.SelectCommand != null)
					{
						if (this.dataAdapter.SelectCommand.Connection != null)
						{
							this.dataAdapter.SelectCommand.Connection.Dispose();
							this.dataAdapter.SelectCommand.Connection = null;
						}
						this.dataAdapter.SelectCommand.Dispose();
						this.dataAdapter.SelectCommand = null;
					}
					this.dataAdapter.Dispose();
					this.dataAdapter = null;
				}
				if (this.insertCommand != null)
				{
					if (this.insertCommand.Connection != null)
					{
						this.insertCommand.Connection.Dispose();
						this.insertCommand.Connection = null;
					}
					this.insertCommand.Dispose();
					this.insertCommand = null;
				}
				if (this.updateCommand != null)
				{
					if (this.updateCommand.Connection != null)
					{
						this.updateCommand.Connection.Dispose();
						this.updateCommand.Connection = null;
					}
					this.updateCommand.Dispose();
					this.updateCommand = null;
				}
				if (this.deleteCommand != null)
				{
					if (this.deleteCommand.Connection != null)
					{
						this.deleteCommand.Connection.Dispose();
						this.deleteCommand.Connection = null;
					}
					this.deleteCommand.Dispose();
					this.deleteCommand = null;
				}
			}
		}

		public DataTable LoadPaymment(long nPKID)
		{
			DataTable dataTable = new DataTable("Paymment");
			string cmdText = "SELECT opm.PKID AS 序号,o.Code AS 订单号,opm.PaymentDate AS 付款日期,opm.PaymentName AS 款项,opm.Amount AS 金额,opm.WayOfPayment AS 付款方式,opm.PaymentAddress as 付款地址,opm.Remark as 备注 from [Order_Payment] as opm inner join [Order] as o on opm.OrderID=o.PKID where opm.PKID=@PKID";
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@PKID", SqlDbType.Int));
				this.selectCommand.Parameters["@PKID"].Value = nPKID;
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public bool AddPayment(string sOrderCode, DateTime dtPaymentDate, string sPaymentName, double dAmount, string sWayOfPayment, string sPaymentAddress, string sRemark, int nOperatorID)
		{
			try
			{
				this.insertCommand = new SqlCommand("InsertOrderPayment", new SqlConnection(AppSettings.ConnectionString));
				this.insertCommand.CommandType = CommandType.StoredProcedure;
				this.insertCommand.Connection.Open();
				this.insertCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.insertCommand.Parameters.Add(new SqlParameter("@PaymentDate", SqlDbType.VarChar, 32));
				this.insertCommand.Parameters.Add(new SqlParameter("@PaymentName", SqlDbType.NVarChar, 32));
				this.insertCommand.Parameters.Add(new SqlParameter("@Amount", SqlDbType.Money));
				this.insertCommand.Parameters.Add(new SqlParameter("@WayOfPayment", SqlDbType.NVarChar, 16));
				this.insertCommand.Parameters.Add(new SqlParameter("@PaymentAddress", SqlDbType.NVarChar, 64));
				this.insertCommand.Parameters.Add(new SqlParameter("@Remark", SqlDbType.NVarChar, 64));
				this.insertCommand.Parameters.Add(new SqlParameter("@OperatorID", SqlDbType.Int));
				this.insertCommand.Parameters["@OrderCode"].Value = sOrderCode;
				this.insertCommand.Parameters["@PaymentDate"].Value = dtPaymentDate.ToShortDateString();
				this.insertCommand.Parameters["@PaymentName"].Value = sPaymentName;
				this.insertCommand.Parameters["@Amount"].Value = dAmount;
				this.insertCommand.Parameters["@WayOfPayment"].Value = sWayOfPayment;
				this.insertCommand.Parameters["@PaymentAddress"].Value = sPaymentAddress;
				this.insertCommand.Parameters["@Remark"].Value = sRemark;
				this.insertCommand.Parameters["@OperatorID"].Value = nOperatorID;
				this.insertCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool UpdatePayment(long nPKID, string sOrderCode, DateTime dtPaymentDate, string sPaymentName, double dAmount, string sWayOfPayment, string sPaymentAddress, string sRemark, int nOperatorID)
		{
			try
			{
				this.insertCommand = new SqlCommand("UpdateOrderPayment", new SqlConnection(AppSettings.ConnectionString));
				this.insertCommand.CommandType = CommandType.StoredProcedure;
				this.insertCommand.Connection.Open();
				this.insertCommand.Parameters.Add(new SqlParameter("@PKID", SqlDbType.Int));
				this.insertCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.insertCommand.Parameters.Add(new SqlParameter("@PaymentDate", SqlDbType.VarChar, 32));
				this.insertCommand.Parameters.Add(new SqlParameter("@PaymentName", SqlDbType.NVarChar, 32));
				this.insertCommand.Parameters.Add(new SqlParameter("@Amount", SqlDbType.Money));
				this.insertCommand.Parameters.Add(new SqlParameter("@WayOfPayment", SqlDbType.NVarChar, 16));
				this.insertCommand.Parameters.Add(new SqlParameter("@PaymentAddress", SqlDbType.NVarChar, 64));
				this.insertCommand.Parameters.Add(new SqlParameter("@Remark", SqlDbType.NVarChar, 64));
				this.insertCommand.Parameters.Add(new SqlParameter("@OperatorID", SqlDbType.Int));
				this.insertCommand.Parameters["@PKID"].Value = nPKID;
				this.insertCommand.Parameters["@OrderCode"].Value = sOrderCode;
				this.insertCommand.Parameters["@PaymentDate"].Value = dtPaymentDate.ToShortDateString();
				this.insertCommand.Parameters["@PaymentName"].Value = sPaymentName;
				this.insertCommand.Parameters["@Amount"].Value = dAmount;
				this.insertCommand.Parameters["@WayOfPayment"].Value = sWayOfPayment;
				this.insertCommand.Parameters["@PaymentAddress"].Value = sPaymentAddress;
				this.insertCommand.Parameters["@Remark"].Value = sRemark;
				this.insertCommand.Parameters["@OperatorID"].Value = nOperatorID;
				this.insertCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool DeletePayment(long nPKID, string sOrderCode, int nOperatorID)
		{
			try
			{
				this.insertCommand = new SqlCommand("DeleteOrderPayment", new SqlConnection(AppSettings.ConnectionString));
				this.insertCommand.CommandType = CommandType.StoredProcedure;
				this.insertCommand.Connection.Open();
				this.insertCommand.Parameters.Add(new SqlParameter("@PKID", SqlDbType.Int));
				this.insertCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.insertCommand.Parameters.Add(new SqlParameter("@OperatorID", SqlDbType.Int));
				this.insertCommand.Parameters["@PKID"].Value = nPKID;
				this.insertCommand.Parameters["@OrderCode"].Value = sOrderCode;
				this.insertCommand.Parameters["@OperatorID"].Value = nOperatorID;
				this.insertCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public DataTable GetPaymentsByCondition(OrderCondition oc)
		{
			this.selectCommand = new SqlCommand();
			string text = "";
			if (oc.CustomerID > -1)
			{
				if (text == "")
				{
					text += "pj.CustomerID=@CustomerID";
				}
				else
				{
					text += " AND pj.CustomerID=@CustomerID";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.Int));
				this.selectCommand.Parameters["@CustomerID"].Value = oc.CustomerID;
			}
			if (oc.ProjectID > -1)
			{
				if (text == "")
				{
					text += "o.ProjectID=@ProjectID";
				}
				else
				{
					text += " AND o.ProjectID=@ProjectID";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@ProjectID", SqlDbType.Int));
				this.selectCommand.Parameters["@ProjectID"].Value = oc.ProjectID;
			}
			if (oc.SpeakForDateStart != null)
			{
				if (text == "")
				{
					text += "o.SpeakForDate>=CONVERT(DATETIME,@SpeakForDateStart) AND o.SpeakForDate<=CONVERT(DATETIME,@SpeakForDateEnd)";
				}
				else
				{
					text += " AND o.SpeakForDate>=CONVERT(DATETIME,@SpeakForDateStart) AND o.SpeakForDate<=CONVERT(DATETIME,@SpeakForDateEnd)";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@SpeakForDateStart", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@SpeakForDateStart"].Value = oc.SpeakForDateStart;
				this.selectCommand.Parameters.Add(new SqlParameter("@SpeakForDateEnd", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@SpeakForDateEnd"].Value = oc.SpeakForDateEnd;
			}
			if (oc.IsLailiao > -1)
			{
				if (text == "")
				{
					text += "o.Lailiao=@Lailiao";
				}
				else
				{
					text += " AND o.Lailiao=@Lailiao";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Lailiao", SqlDbType.Bit));
				this.selectCommand.Parameters["@Lailiao"].Value = oc.IsLailiao;
			}
			if (oc.IsQuliao > -1)
			{
				if (text == "")
				{
					text += "o.Quliao=@Quliao";
				}
				else
				{
					text += " AND o.Quliao=@Quliao";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Quliao", SqlDbType.Bit));
				this.selectCommand.Parameters["@Quliao"].Value = oc.IsQuliao;
			}
			if (oc.Locked > -1)
			{
				if (text == "")
				{
					text += "o.Locked=@Locked";
				}
				else
				{
					text += " AND o.Locked=@Locked";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Locked", SqlDbType.Bit));
				this.selectCommand.Parameters["@Locked"].Value = oc.Locked;
			}
			if (oc.Cancelled > -1)
			{
				if (text == "")
				{
					text += "o.Cancelled=@Cancelled";
				}
				else
				{
					text += " AND o.Cancelled=@Cancelled";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Cancelled", SqlDbType.Bit));
				this.selectCommand.Parameters["@Cancelled"].Value = oc.Cancelled;
			}
			if (oc.PayDateStart != null)
			{
				if (text == "")
				{
					text += "PaymentDate>=CONVERT(DATETIME,@PayedDateStart) AND PaymentDate<=CONVERT(DATETIME,@PayedDateEnd)";
				}
				else
				{
					text += " AND o.PaymentDate>=CONVERT(DATETIME,@PayedDateStart) AND PaymentDate<=CONVERT(DATETIME,@PayedDateEnd)";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@PayedDateStart", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@PayedDateStart"].Value = oc.PayDateStart;
				this.selectCommand.Parameters.Add(new SqlParameter("@PayedDateEnd", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@PayedDateEnd"].Value = oc.PayDateEnd;
			}
			if (oc.OrderCode != null)
			{
				if (text == "")
				{
					text += "o.Code LIKE '%'+@OrderCode+'%'";
				}
				else
				{
					text += " AND o.Code LIKE '%'+@OrderCode+'%'";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@OrderCode"].Value = oc.OrderCode.Replace(" ", "%");
			}
			if (oc.Remark != null)
			{
				if (text == "")
				{
					text += "o.Remark LIKE '%'+@Remark+'%'";
				}
				else
				{
					text += " AND o.Remark LIKE '%'+@Remark+'%'";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Remark", SqlDbType.NVarChar, 64));
				this.selectCommand.Parameters["@Remark"].Value = oc.Remark.Replace(" ", "%");
			}
			if (oc.Salesperson != null)
			{
				if (text == "")
				{
					text += "o.Salesperson LIKE '%'+@Salesperson+'%'";
				}
				else
				{
					text += " AND o.Salesperson LIKE '%'+@Salesperson+'%'";
				}
				this.selectCommand.Parameters.Add(new SqlParameter("@Salesperson", SqlDbType.NVarChar, 16));
				this.selectCommand.Parameters["@Salesperson"].Value = oc.Salesperson.Replace(" ", "%");
			}
			if (oc.LabelPrinted > -1)
			{
				if (text == "")
				{
					if (oc.LabelPrinted == 0)
					{
						text += "o.LabelPrintedQuantity = 0";
					}
					else
					{
						text += "o.LabelPrintedQuantity > 0";
					}
				}
				else if (oc.LabelPrinted == 0)
				{
					text += " AND o.LabelPrintedQuantity = 0";
				}
				else
				{
					text += " AND o.LabelPrintedQuantity > 0";
				}
			}
			if (text != "")
			{
				text = " WHERE " + text;
			}
			DataTable dataTable = new DataTable("SumUp");
			string text2 = "SELECT opm.PKID AS 序号,o.Code AS 订单号,c.CompanyName AS 公司名称,pj.ProjectName AS 计划名称,o.SpeakForDate AS 订单日期,o.DeliveryDate AS 交货日期,dbo.AmountToOrder(o.Code) AS 数量,dbo.TotalSquareOfOrder(o.Code) AS 面积,dbo.TotalValueOfOrder(o.Code) AS 总金额,opm.PaymentName as 款项,opm.Amount as 付款金额,opm.PaymentDate as 付款时间,opm.PaymentAddress as 付款地址,opm.Remark AS 备注 FROM [Order] AS o INNER JOIN Project AS pj ON o.ProjectID=pj.PKID INNER JOIN Customer AS c ON pj.CustomerID=c.PKID INNER JOIN Operator AS op ON o.OperatorID=op.PKID inner join Order_Payment as opm on opm.OrderID=o.PKID";
			text2 = text2 + text + " ORDER BY c.[CompanyName],o.Code";
			try
			{
				this.selectCommand.Connection = new SqlConnection(AppSettings.ConnectionString);
				this.selectCommand.CommandText = text2;
				this.selectCommand.CommandTimeout = 0;
				this.selectCommand.Connection.Open();
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public DataTable GetShipmentMessgeByCondition(OrderCondition oc)
		{
			this.selectCommand = new SqlCommand();
			string sqlCondition = this.GetSqlCondition(oc);
			DataTable dataTable = new DataTable("SumUp");
			string text = "SELECT convert(bit,'0') as 通知发货, ShipmentMessage as 是否通知,o.Code AS 订单号,c.CompanyName AS 公司名称,pj.ProjectName AS 计划名称,o.SpeakForDate AS 订单日期,o.DeliveryDate AS 交货日期,dbo.AmountToOrder(o.Code) AS 数量,dbo.TotalSquareOfOrder(o.Code) AS 面积,o.Locked AS '订单锁定',o.Cancelled AS '订单作废',dbo.GetProductTypeOfOrder_1(o.Code) as 类型,o.Remark AS 订单备注,op.[Name] AS 操作者,o.Salesperson as 业务员 FROM [Order] AS o INNER JOIN Project AS pj ON o.ProjectID=pj.PKID INNER JOIN Customer AS c ON pj.CustomerID=c.PKID INNER JOIN Operator AS op ON o.OperatorID=op.PKID";
			text = text + sqlCondition + " ORDER BY c.[CompanyName],o.PKID";
			try
			{
				this.selectCommand.Connection = new SqlConnection(AppSettings.ConnectionString);
				this.selectCommand.CommandText = text;
				this.selectCommand.CommandTimeout = 0;
				this.selectCommand.Connection.Open();
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public bool ShipMessage(string sOrderCode, bool b)
		{
			string cmdText;
			if (b)
			{
				cmdText = "UPDATE [Order] SET ShipmentMessage=1 WHERE Code=@OrderCode";
			}
			else
			{
				cmdText = "UPDATE [Order] SET ShipmentMessage=0 WHERE Code=@OrderCode";
			}
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters["@OrderCode"].Value = sOrderCode;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public string GetShipMessageNumber()
		{
			string cmdText = "SET XACT_ABORT ON BEGIN TRANSACTION declare @crrentDate datetime set @crrentDate = GETDATE() declare @startDate datetime select @startDate=StartDate FROM ShipmentMessageNumber if YEAR(@startDate)<>YEAR(@crrentDate) OR MONTH(@startDate)<>MONTH(@crrentDate) or day(@startDate)<>day(@crrentDate) begin    update ShipmentMessageNumber set StartDate=@crrentDate,CurrentNumber=0 end update ShipmentMessageNumber SET CurrentNumber=CurrentNumber+1 declare @currentNumber int select @currentNumber=CurrentNumber from ShipmentMessageNumber declare @ShipmentMessageCode varchar(32) set @ShipmentMessageCode = CONVERT( varchar(4),YEAR(@crrentDate) ) + '-' + CONVERT( varchar(2),Month(@crrentDate) ) + '-' + CONVERT( varchar(2),Day(@crrentDate) ) + '-' + CONVERT( varchar(16),@currentNumber ) COMMIT TRANSACTION select @ShipmentMessageCode";
			string result = null;
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				string text = this.selectCommand.ExecuteScalar().ToString();
				if (text != "")
				{
					result = text;
				}
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return result;
		}
	}
}
